﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FOModels.CarLoan;
using System.Data;
namespace DataAccess.CarLoan
{
    public class CarLoanDAL
    {
        private SQLDataAccess db;
        public CarLoanDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getallcarloancate(int langid)
        {
            return db.exeSelect("Select * from CarLoanCategory ca join CarLoanCategoryLang cl on ca.CarLoanCategoryID = cl.CarLoanCategoryID where LangID = " + langid + "");
        }
        public DataTable getTOP1()
        {
            return db.exeSelect("Select TOP 1 * from CarLoan order by CarLoanID desc");
        }
        public DataTable getCarLoanDefault(int cateid, int langid)
        {
            return db.exeSelect("select * from CarLoan sa join CarLoanLang cal on sa.CarLoanID = cal.CarLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableCarLoanEquityInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.CarLoanCategoryID = " + cateid + " and sa.HomeLoanPlanID = 2 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and ta.Years = 1 and Display = 1 and cl.LangID = " + langid + " and cal.LangID = " + langid + "");
        }
        public DataTable getCarLoanDefaultByProvider(int cateid, int CompanyID, int langid)
        {
            return db.exeSelect("select * from CarLoan sa join CarLoanLang cal on sa.CarLoanID = cal.CarLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableCarLoanEquityInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and  sa.CarLoanCategoryID = " + cateid + " and sa.HomeLoanPlanID = 2 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and ta.Years = 1 and Display = 1 and cl.LangID = " + langid + " and cal.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getCarLoan(int cateid, int planid, string balance, int years, int langid)
        {
            return db.exeSelect("select * from CarLoan sa join CarLoanLang cal on sa.CarLoanID = cal.CarLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableCarLoanEquityInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and  sa.CarLoanCategoryID = " + cateid + " and sa.HomeLoanPlanID = " + planid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and ta.Years = " + years + " and Display = 1 and cl.LangID = " + langid + " and cal.LangID = " + langid + "");
        }
        public DataTable getCarLoanByProvider(int cateid, int planid, string balance, int years, int CompanyID,int langid)
        {
            return db.exeSelect("select * from CarLoan sa join CarLoanLang cal on sa.CarLoanID = cal.CarLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableCarLoanEquityInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and  sa.CarLoanCategoryID = " + cateid + " and sa.HomeLoanPlanID = " + planid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and ta.Years = " + years + " and Display = 1 and cl.LangID = " + langid + " and cal.LangID = " + langid + " and sa.CompanyID=" + CompanyID + "");
        }
        public DataTable getCarLoanMortgageDefault(int cateid, int langid)
        {
            return db.exeSelect("select * from CarLoan sa join CarLoanLang cal on sa.CarLoanID = cal.CarLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableCarLoanMortgageInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.CarLoanCategoryID = " + cateid + " and sa.HomeLoanPlanID = 2 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and ta.Years = 1 and Display = 1 and cl.LangID = " + langid + " and cal.LangID = " + langid + "");
        }
        public DataTable getCarLoanMortgageDefaultByProvider(int cateid, int CompanyID, int langid)
        {
            return db.exeSelect("select * from CarLoan sa join CarLoanLang cal on sa.CarLoanID = cal.CarLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableCarLoanMortgageInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and  sa.CarLoanCategoryID = " + cateid + " and sa.HomeLoanPlanID = 2 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and ta.Years = 1 and Display = 1 and cl.LangID = " + langid + " and cal.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getCarLoanMortgage(int cateid, int planid, string balance, int years, int langid)
        {
            return db.exeSelect("select * from CarLoan sa join CarLoanLang cal on sa.CarLoanID = cal.CarLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableCarLoanMortgageInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and  sa.CarLoanCategoryID = " + cateid + " and sa.HomeLoanPlanID = " + planid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and ta.Years = " + years + " and Display = 1 and cl.LangID = " + langid + " and cal.LangID = " + langid + "");
        }
        public DataTable getCarLoanMortgageByProvider(int cateid, int planid, string balance, int years, int CompanyID, int langid)
        {
            return db.exeSelect("select * from CarLoan sa join CarLoanLang cal on sa.CarLoanID = cal.CarLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join TableCarLoanMortgageInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and  sa.CarLoanCategoryID = " + cateid + " and sa.HomeLoanPlanID = " + planid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and ta.Years = " + years + " and Display = 1 and cl.LangID = " + langid + " and cal.LangID = " + langid + " and sa.CompanyID=" + CompanyID + "");
        }
        public DataTable getCarLoanByID(int CarLoanID, int langid)
        {
            return db.exeSelect("select * from CarLoan ca join CarLoanLang cal on ca.CarLoanID = cal.CarLoanID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and  ca.CarLoanID = " + CarLoanID + " and cl.LangID = " + langid + " and cal.LangID = " + langid + "");
        }
        public DataTable getCarLoanByIDManager(int CarLoanID, int langid)
        {
            return db.exeSelect("select * from CarLoan ca join CarLoanLang cal on ca.CarLoanID = cal.CarLoanID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where ca.CarLoanID = " + CarLoanID + " and cl.LangID = " + langid + " and cal.LangID = " + langid + "");
        }
        public DataTable getcarloantrustrelati(int cateid, int CompanyID, int CarLoanID,int langid)
        {
            return db.exeSelect("select * from CarLoan ca join CarLoanLang cal on ca.CarLoanID = cal.CarLoanID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and  cl.LangID = " + langid + " and cal.LangID = " + langid + " and ca.CompanyID =" + CompanyID + " and ca.CarLoanID !=" + CarLoanID + " sa.HomeLoanCategoryID = " + cateid + " and Display=1 ");
        }
        public DataTable getall(int cateid, int langid)
        {
            return db.exeSelect("Select * from CarLoan sa join CarLoanLang cal on sa.CarLoanID = cal.CarLoanID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join Language la on cal.LangID = la.ID where sa.CarLoanCategoryID = " + cateid + " and cl.LangID = " + langid + " and cal.LangID = " + langid + "");
        }
        public int UpdateCarLoan(CarLoanModelscs models)
        {
            return db.exeUpdate("update CarLoan set CarLoanName = N'" + models.CarLoanName + "',MinimumBalance=" + double.Parse(models.MinimumBalance) + ",MaximumBalance=" + double.Parse(models.MaximumBalance) + ",HomeLoanPlanID=" + models.HomeLoanPlanID + ",CompanyID= " + models.CompanyID + ",CarLoanCategoryID=" + models.CarLoanCategoryID + ",Display='" + models.Display + "' where CarLoanID = " + models.CarLoanID + "");
        }
        public int InsertCarLoanMortgage(CarLoanModelscs models)
        {
            return db.exeUpdate("insert into CarLoan values (N'" + models.CarLoanName + "'," + models.CompanyID + ",2," + double.Parse(models.MinimumBalance) + "," + double.Parse(models.MaximumBalance) + "," + models.HomeLoanPlanID + ",'" + models.Display + "')");
        }
        public int InsertCarLoanTrust(CarLoanModelscs models)
        {
            return db.exeUpdate("insert into CarLoan values (N'" + models.CarLoanName + "'," + models.CompanyID + ",1," + double.Parse(models.MinimumBalance) + "," + double.Parse(models.MaximumBalance) + "," + models.HomeLoanPlanID + ",'" + models.Display + "')");
        }
        public int DeleteCarLoan(int CarLoanID)
        {
            return db.exeUpdate("Delete from CarLoan where CarLoanID = " + CarLoanID + "");
        }
        public int UpdateCarLoanLang(CarLoanModelscs models, int langid)
        {
            return db.exeUpdate("update CarLoanLang set CarLoanName = N'" + models.CarLoanName + "', Benefit = N'" + models.Benefit + "',Description=N'" + models.Description + "',FeeContent=N'" + models.FeeContent + "',MoreInfo=N'" + models.MoreInfo + "'where CarLoanID = " + models.CarLoanID + " and LangID=" + langid + "");
        }
        public int InsertCarLoanLang(CarLoanModelscs models, int langid)
        {
            return db.exeUpdate("insert into CarLoanLang values (" + models.CarLoanID + "," + langid + ",N'" + models.CarLoanName + "',N'" + models.Benefit + "',N'" + models.Description + "',N'" + models.FeeContent + "',N'" + models.MoreInfo + "')");
        }
        public int DeleteCarLoanLang(int CarLoanID)
        {
            return db.exeUpdate("Delete from CarLoanLang where CarLoanID = " + CarLoanID + "");
        }

        public int DeleteCarLoanApply(int CarLoanApplyID)
        {
            return db.exeUpdate("Delete from CarLoanApply where CarLoanApplyID = " + CarLoanApplyID + "");
        }
        public DataTable getcontractbyid(int CarLoanapplyid)
        {
            return db.exeSelect("select * from CarLoanApply ca join [CarLoan] c on ca.CarLoanID = c.CarLoanID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.CarLoanApplyID = " + CarLoanapplyid + " and cl.LangID = 1");
        }
        public DataTable getcontractbycateid(int cateid)
        {
            return db.exeSelect("select * from CarLoanApply ca join [CarLoan] c on ca.CarLoanID = c.CarLoanID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where c.CarLoanCategoryID = " + cateid + " and cl.LangID = 1");
        }
        public DataTable getcontractbystatuscount(int cateid, int statusid)
        {
            return db.exeSelect("select * from CarLoanApply ca join [CarLoan] c on ca.CarLoanID = c.CarLoanID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where c.CarLoanCategoryID = " + cateid + " and ca.StatusID = " + statusid + " and cl.LangID = 1");
        }
        public int CarLoanApply(int CarLoanID, CarLoanApplyModels app)
        {
            return db.exeUpdate("insert into CarLoanApply(CarLoanID,CustomerName,CustomerEmail,CustomerPhone,CustomerIncome,CustomerDOB,StatusID) values (" + CarLoanID + ",N'" + app.CustomerName + "',N'" + app.CustomerEmail + "',N'" + app.CustomerPhone + "',N'" + app.CustomerIncome + "',N'" + app.CustomerDOB + "'," + app.StatusID + ")");
        }
        public int UpdateStatus(CarLoanApplyModels CarLoan)
        {
            return db.exeUpdate("update CarLoanApply set StatusID= " + CarLoan.StatusID + " where CarLoanApplyID = " + CarLoan.CarLoanApplyID + "");
        }
    }
}
